﻿using System;
using System.Data.SqlClient;
using Microsoft.Win32.SafeHandles;
using System.Runtime.InteropServices;
using System.IO;

namespace Knowledge.Resources
{
    static class DBFileStream
    {
        const UInt32 DESIRED_ACCESS_READ = 0x00000000;
        const UInt32 DESIRED_ACCESS_WRITE = 0x00000001;
        const UInt32 DESIRED_ACCESS_READWRITE = 0x00000002;

        const UInt32 SQL_FILESTREAM_OPEN_NO_FLAGS = 0x00000000;

        [DllImport("sqlncli10.dll", SetLastError = true, CharSet = CharSet.Unicode)]
        static extern SafeFileHandle OpenSqlFilestream(
                    string FilestreamPath,
                    UInt32 DesiredAccess,
                    UInt32 OpenOptions,
                    byte[] FilestreamTransactionContext,
                    UInt32 FilestreamTransactionContextLength,
                    Int64 AllocationSize);        
        
        // usage: BLOB [get | put] filename [id]
        /*static void Main(string[] args)
        {
            switch (args[0].ToLower())
            {
                case "get":
                    Get(args[1], args[2]);
                    break;

                case "put":
                    Put(args[1]);
                    break;
            }
        }*/

        static void Get(string filename, string blobID)
        {
            SqlConnection cn = null;
            SqlTransaction tx = null;
            SqlCommand cmd = null;

            try
            {
                cn = GetConnection();
                tx = cn.BeginTransaction();
                cmd = new SqlCommand("dbo.stp_GetBLOB", cn, tx);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ID", new System.Guid(blobID));
                SqlDataReader r = cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow);
                r.Read();
                string path = r[0].ToString();
                r.Close();

                // get the transaction context
                cmd = new SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", cn, tx);
                Object obj = cmd.ExecuteScalar();
                byte[] txCtx = (byte[])obj;

                // open the filestream to the blob
                SafeFileHandle handle = OpenSqlFilestream(path, DESIRED_ACCESS_READ, SQL_FILESTREAM_OPEN_NO_FLAGS, txCtx, (UInt32)txCtx.Length, 0);

                // open a Filestream to read the blob
                FileStream blob = new FileStream(handle, FileAccess.Read);

                byte[] buffer = new byte[(int)blob.Length];
                blob.Read(buffer, 0, buffer.Length);
                blob.Close();

                if (handle != null && !handle.IsClosed)
                    handle.Close();

                // write out the blob to a file
                FileStream output = new FileStream(filename, FileMode.CreateNew);
                output.Write(buffer, 0, buffer.Length);
                output.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (cn != null)
                {
                    tx.Commit();
                    cn.Close();
                }
            }
        }

        static void Put(string filename)
        {
            SqlConnection cn = null;
            SqlTransaction tx = null;
            SqlCommand cmd = null;
            SqlCommand cmd2 = null;
            bool bCommit = false;

            try
            {
                // read in the file to be saved as a blob in the database
                FileStream input = new FileStream(filename, FileMode.Open, FileAccess.Read);
                byte[] buffer = new byte[(int)input.Length];
                input.Read(buffer, 0, buffer.Length);

                cn = GetConnection();
                tx = cn.BeginTransaction();
                cmd = new SqlCommand("dbo.stp_AddBLOB", cn, tx);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                SqlDataReader r = cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow);
                r.Read();
                string id = r[0].ToString();
                string path = r[1].ToString();
                r.Close();

                // get the transaction context
                cmd2 = new SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", cn, tx);
                Object obj = cmd2.ExecuteScalar();
                byte[] txCtx = (byte[])obj;

                // open the filestream to the blob
                SafeFileHandle handle = OpenSqlFilestream(path, DESIRED_ACCESS_WRITE, SQL_FILESTREAM_OPEN_NO_FLAGS, txCtx, (UInt32)txCtx.Length, 0);

                // open a Filestream to write the blob
                FileStream output = new FileStream(handle, FileAccess.Write, buffer.Length, false);
                output.Write(buffer, 0, buffer.Length);
                output.Close();

                if (handle != null && !handle.IsClosed)
                    handle.Close();

                bCommit = true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (cn != null)
                {
                    switch (bCommit)
                    {
                        case true:
                            tx.Commit();
                            break;

                        case false:
                            tx.Rollback();
                            break;
                    }

                    cn.Close();
                }
            }
        }

        static SqlConnection GetConnection()
        {
            // TODO: edit connection string to specify your database
            SqlConnection cn = new SqlConnection("Integrated Security=true;server=(local);Initial Catalog=fs");
            cn.Open();

            return cn;
        }
    }
}
